using System;
using Avanzis.QueryFramework;
using NUnit.Framework;

namespace Avanzis.QueryBuilder.NUnit.MsSql2000
{
    public class MsSql2000SelectBuilderTest : SelectBuilderTest
    {
        private Dialects previousDialect = Dialects.MSSql2005Dialect;

        [TestFixtureSetUp()]
        public virtual void Setup()
        {
            previousDialect = QueryFrameworkConfiguration.Dialect;
            QueryFrameworkConfiguration.Dialect = Dialects.MSSql2000Dialect;
            QueryProcessor.ResetProcessor();
        }


        [TestFixtureTearDown()]
        public virtual void TearDown()
        {
            QueryFrameworkConfiguration.Dialect = previousDialect;
        }

        [Test]
        public override void SimpleLimit10()
        {
            SelectBuilder sb = new SelectBuilder();
            sb.Select.Fields.Add("ArticleId");
            sb.Select.Fields.Add("Name");
            sb.Select.Limit = 10;
            sb.From.TableName = "Articles";

            string result = "SELECT TOP 10 ArticleId,Name\n" +
                "FROM [Articles]\n";                

            Assert.AreEqual(result, sb.ToString());
            SelectBuilder cloned = (SelectBuilder)sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        [ExpectedException(typeof(InvalidOperationException))]
        public override void SimpleOffset10()
        {
            QueryFrameworkConfiguration.Dialect = Dialects.MSSql2000Dialect;

            SelectBuilder sb = new SelectBuilder();
            sb.Select.Fields.Add("ArticleId");
            sb.Select.Fields.Add("Name");            
            sb.Select.Offset = 10;
            sb.From.TableName = "Articles";

            string result = "SELECT ArticleId,Name\n" +
                "FROM [Articles]\n";

            Assert.AreEqual(result, sb.ToString());
            SelectBuilder cloned = (SelectBuilder)sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        [ExpectedException(typeof(InvalidOperationException))]
        public override void SimpleLimit20Offset10()
        {
            QueryFrameworkConfiguration.Dialect = Dialects.MSSql2000Dialect;

            SelectBuilder sb = new SelectBuilder();
            sb.Select.Fields.Add("ArticleId");
            sb.Select.Fields.Add("Name");
            sb.Select.Limit = 20;
            sb.Select.Offset = 10;
            sb.From.TableName = "Articles";

            string result = "SELECT TOP 20 ArticleId,Name\n" +
                "FROM [Articles]\n";

            Assert.AreEqual(result, sb.ToString());
            SelectBuilder cloned = (SelectBuilder)sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        [ExpectedException(typeof(InvalidOperationException))]
        public override void SimpleLimit20Offset10WithOrderBy()
        {
            QueryFrameworkConfiguration.Dialect = Dialects.MSSql2000Dialect;                        

            SelectBuilder sb = new SelectBuilder();
            sb.Select.Fields.Add("ArticleId");
            sb.Select.Fields.Add("Name");
            sb.Select.Limit = 20;
            sb.Select.Offset = 10;
            sb.From.TableName = "Articles";
            sb.OrderBy.Fields.Add("Name", OrderDirection.Asc);

            string result = "SELECT ArticleId,Name\n" +
                "FROM [Articles]\n" +
                "ORDER BY Name\n" +
                "LIMIT 20\n" +
                "OFFSET 10\n";

            Assert.AreEqual(result, sb.ToString());
            SelectBuilder cloned = (SelectBuilder)sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public override void SingleJoinWithMultipleConditions()
        {
            SelectBuilder sb = new SelectBuilder();
            sb.Select.Fields.Add("Articles.ArticleId");
            sb.Select.Fields.Add("Articles.Name");
            sb.Select.Fields.Add("Providers.Name");
            sb.From.TableName = "Articles";

            JoinTable jt = sb.From.Joins.Add(JoinTypes.Left, "Providers", "Articles.ProviderId = Providers.ProviderId");
            jt.Filters.Add("Articles.ArticleType = Providers.ArticleType");

            jt.SearchCondition.ExternalUnion = FilterUnion.And;

            string result = "SELECT Articles.ArticleId,Articles.Name,Providers.Name\n" +
                "FROM [Articles] " +
                "LEFT JOIN [Providers] ON ((Articles.ProviderId = Providers.ProviderId) AND (Articles.ArticleType = Providers.ArticleType))\n";

            Assert.AreEqual(result, sb.ToString());
            SelectBuilder cloned = (SelectBuilder)sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public override void ReuseFieldsInWhereAndGroupBy()
        {
            SqlField field1 = new SqlField("Articles.ArticleId");
            SqlField field2 = new SqlField("AVG(UnitCost)", "Media Unit Cost");

            SelectBuilder sb = new SelectBuilder();
            sb.Select.Fields.Add(field1);
            sb.Select.Fields.Add(field2);
            sb.From.TableName = "Articles";
            sb.Where.Filters.Add(field1, SqlOperator.Equals, 92);
            sb.GroupBy.Fields.Add(field1);

            string result = "SELECT Articles.ArticleId,AVG(UnitCost) AS 'Media Unit Cost'\n" +
                "FROM [Articles]\n" +
                "WHERE (Articles.ArticleId = 92)\n" +
                "GROUP BY Articles.ArticleId\n";

            Assert.AreEqual(result, sb.ToString());
            SelectBuilder cloned = (SelectBuilder)sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public override void MultipleJoins()
        {
            SelectBuilder sb = new SelectBuilder();
            sb.Select.Fields.Add("Articles.ArticleId");
            sb.Select.Fields.Add("Articles.Name");
            sb.Select.Fields.Add("ArticleFamilies.Name");
            sb.Select.Fields.Add("Providers.Name");
            sb.From.TableName = "Articles";
            sb.From.Joins.Add("ArticleFamilies", "Articles.ArticleFamilyId", SqlOperator.Equals, "ArticleFamilies.ArticleFamilyId");
            sb.From.Joins.Add(JoinTypes.Left, "Providers", "Articles.ProviderId = Providers.ProviderId");
            string result = "SELECT Articles.ArticleId,Articles.Name,ArticleFamilies.Name,Providers.Name\n" +
                "FROM [Articles] " +
                "INNER JOIN [ArticleFamilies] ON ((Articles.ArticleFamilyId = ArticleFamilies.ArticleFamilyId)) " +
                "LEFT JOIN [Providers] ON ((Articles.ProviderId = Providers.ProviderId))\n";

            Assert.AreEqual(result, sb.ToString());
            SelectBuilder cloned = (SelectBuilder)sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public override void SimpleJoin()
        {
            SelectBuilder sb = new SelectBuilder();
            sb.Select.Fields.Add("Articles.ArticleId");
            sb.Select.Fields.Add("Articles.Name");
            sb.Select.Fields.Add("ArticleFamilies.Name");
            sb.From.TableName = "Articles";
            sb.From.Joins.Add("ArticleFamilies", "Articles.ArticleFamilyId", SqlOperator.Equals, "ArticleFamilies.ArticleFamilyId");

            string result = "SELECT Articles.ArticleId,Articles.Name,ArticleFamilies.Name\n" +
                "FROM [Articles] INNER JOIN [ArticleFamilies] ON ((Articles.ArticleFamilyId = ArticleFamilies.ArticleFamilyId))\n";

            Assert.AreEqual(result, sb.ToString());
            SelectBuilder cloned = (SelectBuilder)sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public override void SimpleSelectAll()
        {
            SelectBuilder sb = new SelectBuilder();
            sb.Select.Fields.Add("*");
            sb.From.TableName = "Articles";
            sb.Where.Filters.Add("Active = 1");
            string result = "SELECT *\n" +
                "FROM [Articles]\n" +
                "WHERE (Active = 1)\n";

            Assert.AreEqual(result, sb.ToString());

            SelectBuilder cloned = (SelectBuilder)sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());


        }

        [Test]
        public override void SimpleSelectFields()
        {
            SelectBuilder sb = new SelectBuilder();
            sb.Select.Fields.Add("ArticleId");
            sb.Select.Fields.Add("Name");
            sb.From.TableName = "Articles";

            string result = "SELECT ArticleId,Name\n" +
                "FROM [Articles]\n";

            Assert.AreEqual(result, sb.ToString());
            SelectBuilder cloned = (SelectBuilder)sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }



        [Test]
        public override void SimpleDistinct()
        {
            SelectBuilder sb = new SelectBuilder();
            sb.Select.Fields.Add("ArticleId");
            sb.Select.Fields.Add("Name");
            sb.Select.SelectionType = SelectionTypes.Distinct;
            sb.From.TableName = "Articles";

            string result = "SELECT DISTINCT ArticleId,Name\n" +
                "FROM [Articles]\n";

            Assert.AreEqual(result, sb.ToString());
            SelectBuilder cloned = (SelectBuilder)sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }


        [Test]
        public override void SelectFieldsWithFilter()
        {
            SelectBuilder sb = new SelectBuilder();
            sb.Select.Fields.Add("ArticleId");
            sb.Select.Fields.Add("Name");
            sb.From.TableName = "Articles";
            sb.Where.Filters.Add("Active = 1");

            string result = "SELECT ArticleId,Name\n" +
                "FROM [Articles]\n" +
                "WHERE (Active = 1)\n";

            Assert.AreEqual(result, sb.ToString());
            SelectBuilder cloned = (SelectBuilder)sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        public override void SelectFieldsWithFilterAndOperator()
        {
            SelectBuilder sb = new SelectBuilder();
            sb.Select.Fields.Add("ArticleId");
            sb.Select.Fields.Add("Name");
            sb.From.TableName = "Articles";
            sb.Where.Filters.Add("Active", SqlOperator.GreaterOrEqual, 0);

            string result = "SELECT ArticleId,Name\n" +
                "FROM `Articles`\n" +
                "WHERE (Active >= 0)\n";

            Assert.AreEqual(result, sb.ToString());
            SelectBuilder cloned = (SelectBuilder)sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public override void SimpleOrderBy()
        {
            SelectBuilder sb = new SelectBuilder();
            sb.Select.Fields.Add("*");
            sb.From.TableName = "Articles";
            sb.OrderBy.Fields.Add("ArticleId DESC");

            string result = "SELECT *\n" +
                "FROM [Articles]\n" +
                "ORDER BY ArticleId DESC\n";

            Assert.AreEqual(result, sb.ToString());
            SelectBuilder cloned = (SelectBuilder)sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public override void SimpleGroupBy()
        {
            SelectBuilder sb = new SelectBuilder();
            sb.Select.Fields.Add("ArticleId");
            sb.Select.Fields.Add("AVG(UnitCost)", "Coste medio por unidad");
            sb.From.TableName = "Articles";
            sb.GroupBy.Fields.Add("ArticleId");

            string result = "SELECT ArticleId,AVG(UnitCost) AS 'Coste medio por unidad'\n" +
                "FROM [Articles]\n" +
                "GROUP BY ArticleId\n";

            Assert.AreEqual(result, sb.ToString());
            SelectBuilder cloned = (SelectBuilder)sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public override void SimpleHaving()
        {
            SelectBuilder sb = new SelectBuilder();
            sb.Select.Fields.Add("ArticleId");
            sb.Select.Fields.Add("AVG(UnitCost)", "Coste medio por unidad");
            sb.From.TableName = "Articles";
            sb.GroupBy.Fields.Add("ArticleId");
            sb.Having.Filters.Add("Active = 1");

            string result = "SELECT ArticleId,AVG(UnitCost) AS 'Coste medio por unidad'\n" +
                "FROM [Articles]\n" +
                "GROUP BY ArticleId\n" +
                "HAVING (Active = 1)\n";

            Assert.AreEqual(result, sb.ToString());
            SelectBuilder cloned = (SelectBuilder)sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }



    }
}
